<?php

if( isset($_GET['scrollx']) and isset($_GET['scrolly']) ) {
	$_SESSION['scrollx'] = $_GET['scrollx'];
	$_SESSION['scrolly'] = $_GET['scrolly'];
}

if( isset($_POST['scrollx']) and isset($_POST['scrolly']) ) {
	$_SESSION['scrollx'] = $_POST['scrollx'];
	$_SESSION['scrolly'] = $_POST['scrolly'];
}

////////////////////////////////////// SCORE /////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////////////
if(isset($_GET['ready_score'])) // corresponding to "isset($_GET['add_student'])" or "isset($_GET[add_subject])"
{
	// this line important, it fires after logged in and clicked "Manage scores"
	$_SESSION['which'] = 'ready_score';
	
	// refresh index = 0 if "Quản lý điểm" menu is clicked
	if(isset($_GET['index']))
	{
		$_SESSION['index'] = $_GET['index'];
	}
}

if(isset($_POST['action']) and $_POST['action'] == 'Enter')
{
	$student_id = mysqli_real_escape_string($link, $_POST['student_id']);
	$sql = "SELECT mssv FROM sinh_vien WHERE mssv = '$student_id'";
	$result = mysqli_query($link, $sql);
	if(!$result)
	{
		$error = 'Error fetching specified student id';
		include '/includes/error.html.php';
		exit();
	}
	// check if this student exists
	if(mysqli_num_rows($result) == 0)
	{
		$_SESSION['error'] = 'Mã SV vừa nhập không có trong CSDL';
		header('Location: .');
		exit();
	}	
	
	include 'prepare_student_information.php';

	// this line important, it fires after press "Enter"
	$_SESSION['which'] = 'score';
	
	header('Location: .');
	exit();
}

// this if-clause happens when:
////////////1) click link "Nhập điểm" on board
////////////2) click button "Nhập điểm" on form
////////////3) click button "Sửa điểm" on form
////////////4) click button "Xóa điểm" on form
////////////5) click page count at the bottom of student_board
if(isset($_GET['add_score']) or (isset($_SESSION['which']) and $_SESSION['which'] == 'score'))
{
	// get student_id and student_number from session
	$student_id = $_SESSION['student_id'];

	// prepare variables for 'score_form.html.php'
	$pagetitle = 'Nhập điểm cho MSSV ' . $student_id;
	$action = 'add_score_form';
	$subject_id = '';
	$test_score = 0;
	$exam_score1 = 0;
	$exam_score2 = 0;
	$term = 1;
	$button = 'Nhập điểm';

	///////////// create subjects array ///////////
	$sql = "SELECT mmh, ten_mh FROM mon_hoc";
	$result = mysqli_query($link, $sql);
	if(!$result)
	{
		$error = 'error fetching subject to add';
		include './includes/error.html.php';
		exit();
	}

	$subjects = array();
	while($row = mysqli_fetch_array($result))
	{
		$subjects[] = array('subject_id' => $row['mmh'], 'subject_name' => $row['ten_mh']);
	}
	
	// refresh index = 0 if "Quản lý ..." menu is clicked
	if(isset($_GET['index']))
	{
		$_SESSION['index'] = $_GET['index'];
	}
}

if(isset($_GET['add_score_form']))
{
	// get student_id from session
	$student_id = $_SESSION['student_id'];

	// get information from 'score_form.html.php'
	$subject_id = mysqli_real_escape_string($link, $_POST['subject_id']);
	$test_score = mysqli_real_escape_string($link, $_POST['test_score']);
	$exam_score1 = mysqli_real_escape_string($link, $_POST['exam_score1']);
	$exam_score2 = mysqli_real_escape_string($link, $_POST['exam_score2']);
	$term = mysqli_real_escape_string($link, $_POST['term']);

	// if no subject name is chosen
	if($subject_id == '')
	{
		$_SESSION['error'] = 'Bạn chưa chọn môn học!';
		header('Location: .');
		exit();
	}

	$sql = "INSERT INTO diem SET mssv = '$student_id',
								 mmh = '$subject_id',
								 diem_kiem_tra = '$test_score',
								 diem_thi1 = '$exam_score1',								 
								 diem_thi2 = '$exam_score2',								
								 hoc_ky = '$term'";

	if(!mysqli_query($link, $sql))
	{
		$_SESSION['error'] = 'Có lỗi khi thêm dữ liệu! Vui lòng kiểm tra lại';
		header('Location: .');
		exit();
		//$error = 'Error inserting row';
	}

	include 'prepare_student_information.php';

	$_SESSION['which'] = 'score';
	header('Location: .');
	exit();
}

if(isset($_POST['action']) and $_POST['action'] == 'Sửa điểm')
{
	// get student_number from session
	$student_id = $_SESSION['student_id'];
	
	// get input values
	$subject_id = mysqli_real_escape_string($link, $_POST['subject_id']);
	$term = mysqli_real_escape_string($link, $_POST['term']);

	$sql = "SELECT phan_tram_kiem_tra, diem_kiem_tra,
				   diem_thi1, diem_thi2, hoc_ky
			FROM diem INNER JOIN mon_hoc ON diem.mmh = mon_hoc.mmh
			WHERE mssv = '$student_id' AND diem.mmh = '$subject_id' AND hoc_ky = '$term'";

	$result = mysqli_query($link, $sql);
	if(!$result)
	{
		$error = 'Error fetching information to edit';
		include './includes/error.html.php';
		exit();
	}
	$row = mysqli_fetch_array($result);

	// prepare variables for 'score_form.html.php'
	$pagetitle = 'Sửa điểm của MSSV ' . $student_id;
	$action = 'edit_score_form';
	$button = 'Sửa điểm';
	$test_percentage = $row['phan_tram_kiem_tra'];
	$test_score = $row['diem_kiem_tra'];
	$exam_score1 = $row['diem_thi1'];
	$exam_score2 = $row['diem_thi2'];

	// prepare subject names for combobox
	$sql = "SELECT mmh, ten_mh FROM mon_hoc";
	$result = mysqli_query($link, $sql);
	if(!$result)
	{
		$error = 'Error fetching subject to edit';
		include './includes/error.html.php';
		exit();
	}

	$subjects = array();
	while($row = mysqli_fetch_array($result))
	{
		$subjects[] = array('subject_id' => $row['mmh'], 'subject_name' => $row['ten_mh']);
	}
}

if (isset($_GET['edit_score_form']))
{
	// get old_subject_id from 'score_form.html.php'
	$old_subject_id = mysqli_real_escape_string($link, $_POST['old_subject_id']);
	$old_term = mysqli_real_escape_string($link, $_POST['old_term']);

	$student_id = mysqli_real_escape_string($link, $_POST['student_id']);
	$subject_id = mysqli_real_escape_string($link, $_POST['subject_id']);
	$test_score = mysqli_real_escape_string($link, $_POST['test_score']);
	$exam_score1 = mysqli_real_escape_string($link, $_POST['exam_score1']);
	$exam_score2 = mysqli_real_escape_string($link, $_POST['exam_score2']);
	$term = mysqli_real_escape_string($link, $_POST['term']);

	// if no subject name is chosen
	if($subject_id == '')
	{
		$_SESSION['error'] = 'Bạn chưa chọn môn học!';
		header('Location: .');
		exit();
	}

	$sql = "UPDATE diem SET
			mmh = '$subject_id',
			diem_kiem_tra = '$test_score',
			diem_thi1 = '$exam_score1',		
			diem_thi2 = '$exam_score2',
			hoc_ky = '$term'
			WHERE mssv = '$student_id' AND mmh = '$old_subject_id' AND hoc_ky = '$old_term'";

	if (!mysqli_query($link, $sql))
	{
		$_SESSION['error'] = 'Có lỗi khi cập nhật dữ liệu! Vui lòng kiểm tra lại.';
		header('Location: .');
		exit();
		//$error = 'Error updating score.';
	}
	include 'prepare_student_information.php';

	$_SESSION['which'] = 'score';
	header('Location: .');
	exit();
}

if(isset($_POST['action']) and $_POST['action'] == 'Xóa điểm')
{
	// get information from 'scores.html.php'
	$student_id = mysqli_real_escape_string($link, $_POST['student_id']);
	$subject_id = mysqli_real_escape_string($link, $_POST['subject_id']);
	$term = mysqli_real_escape_string($link, $_POST['term']);
	$sql = "DELETE FROM diem WHERE mssv = '$student_id' AND mmh = '$subject_id' AND hoc_ky = '$term'";

	if(!mysqli_query($link, $sql))
	{
		$_SESSION['error'] = 'Có lỗi khi xóa dữ liệu! Vui lòng kiểm tra lại';
		header('Location: .');
		exit();
		//$error = 'Error deleting the row of scores';
	}

	$_SESSION['which'] = 'score';
	header('Location: .');
	exit();
}

////////////////////////////////////// SUBJECT /////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////////////
// this if-clause happens when:
////////////1) click "Quản lý môn học"
////////////2) click link "Thêm môn học" on board
////////////3) click button "Thêm môn học" on form
////////////4) click button "Sửa môn học" on form
////////////5) click button "Xóa môn học" on form
////////////6) click page number at the bottom of student_board
if(isset($_GET['add_subject']) or (isset($_SESSION['which']) and $_SESSION['which'] == 'subject'))
{
	// store into session
	$_SESSION['pagetitle'] = 'Thêm môn học';
	$_SESSION['action'] = 'add_subject_form';
	$_SESSION['subject_id'] = '';
	$_SESSION['subject_name'] = '';
	$_SESSION['test_percentage'] = 50;
	$_SESSION['button'] = 'Thêm môn học';

	// this line important, it fires after logged in and clicked "Manage subjects"
	$_SESSION['which'] = 'subject';

	// refresh index = 0 if "Quản lý ..." menu is clicked
	if(isset($_GET['index']))
	{
		$_SESSION['index'] = $_GET['index'];
	}
}

if(isset($_GET['add_subject_form']))
{
	$subject_id = mysqli_real_escape_string($link, $_POST['subject_id']);
	$subject_name = mysqli_real_escape_string($link, $_POST['subject_name']);
	$test_percentage = mysqli_real_escape_string($link, $_POST['test_percentage']);
	$sql = "INSERT INTO mon_hoc SET mmh='$subject_id',
									ten_mh='$subject_name',
									phan_tram_kiem_tra = '$test_percentage'";

	if(!mysqli_query($link, $sql))
	{
		$_SESSION['error'] = 'Có lỗi khi thêm môn học! Vui lòng kiểm tra lại';
		header('Location: .');
		exit();
		//$error = 'Error adding submitted subject';
	}

	header('Location: .');
	exit();
}

if(isset($_POST['action']) and $_POST['action'] == 'Sửa MH')
{
	$subject_id = mysqli_real_escape_string($link, $_POST['subject_id']);
	$sql = "SELECT * FROM mon_hoc WHERE mmh = '$subject_id'";
	$result = mysqli_query($link, $sql);
	if(!$result)
	{
		$error = 'Error fetching subject to edit';
		include './includes/error.html.php';
		exit();
	}
	$row = mysqli_fetch_array($result);

	// store into session
	$_SESSION['pagetitle'] = 'Sửa thông tin môn học';
	$_SESSION['action'] = 'edit_subject_form';
	$_SESSION['subject_id'] = $row['mmh'];
	$_SESSION['subject_name'] = $row['ten_mh'];
	$_SESSION['test_percentage'] = $row['phan_tram_kiem_tra'];
	$_SESSION['button'] = 'Sửa môn học';
}


if(isset($_GET['edit_subject_form']))
{
	$old_subject_id = mysqli_real_escape_string($link, $_POST['old_subject_id']);
	$subject_id = mysqli_real_escape_string($link, $_POST['subject_id']);
	$subject_name = mysqli_real_escape_string($link, $_POST['subject_name']);
	$test_percentage = mysqli_real_escape_string($link, $_POST['test_percentage']);
	$sql = "UPDATE mon_hoc SET
			mmh = '$subject_id',
			ten_mh = '$subject_name',
			phan_tram_kiem_tra = $test_percentage
			WHERE mmh = '$old_subject_id'";

	$result = mysqli_query($link, $sql);
	if(!$result)
	{
		$_SESSION['error'] = 'Có lỗi khi sửa thông tin môn học!';
		header('Location: .');
		exit();
		//$error = 'Error editing subject';
	}

	header('Location: .');
	exit();
}

if(isset($_POST['action']) and $_POST['action'] == 'Xóa MH')
{
	$subject_id = mysqli_real_escape_string($link, $_POST['subject_id']);
	$sql = "DELETE FROM diem WHERE mmh = '$subject_id'";
	$result = mysqli_query($link, $sql);
	if(!$result)
	{
		$_SESSION['error'] = 'Có lỗi khi xóa dữ liệu! Vui lòng kiểm tra lại';
		header('Location: .');
		exit();
		//$error = 'Error deleting row on &lsquodiem&rsquo';
	}

	$sql = "DELETE FROM mon_hoc WHERE mmh = '$subject_id'";
	$result = mysqli_query($link, $sql);
	if(!$result)
	{
		$_SESSION['error'] = 'Có lỗi khi xóa dữ liệu! Vui lòng kiểm tra lại!';
		header('Location: .');
		exit();
		//$error = 'Error deleting subject';
	}

	header('Location: .');
	exit();
}

////////////////////////////////////// STUDENT /////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////////////
////////////// student form is default after login, so this if-clause is special ////////////////
// this if-clause happens when:
////////////1) click "Login" with root's user name and password
////////////2) click "Quản lý sinh viên"
////////////3) click link "Thêm sinh viên" on board
////////////4) click button "Thêm sinh viên" on form
////////////5) click button "Sửa sinh viên" on form
////////////6) click button "Xóa sinh viên" on form
////////////7) click page number at the bottom of student_board
if(isset($_GET['add_student']) or (isset($_SESSION['which']) and $_SESSION['which'] == 'student'))
{
	// store into session
	$_SESSION['pagetitle'] = 'Thêm sinh viên';
	$_SESSION['action'] = 'add_student_form';
	$_SESSION['student_id'] = '';
	$_SESSION['name'] = '';
	$_SESSION['birthday'] = '0000-00-00';
	$_SESSION['edu_system'] = '';
	$_SESSION['branch'] = '';
	$_SESSION['course'] = '';
	$_SESSION['button'] = 'Thêm sinh viên';

	// this line important, it fires after logged in and clicked "Manage students"
	$_SESSION['which'] = 'student';

	// refresh index = 0 if "Quản lý ..." menu is clicked
	if(isset($_GET['index']))
	{
		$_SESSION['index'] = $_GET['index'];
	}
	
}

if (isset($_GET['add_student_form']))
{
	$student_id = mysqli_real_escape_string($link, $_POST['student_id']);
	$name = mysqli_real_escape_string($link, $_POST['name']);
	$birthday = mysqli_real_escape_string($link, $_POST['birthday']);
	$edu_system = mysqli_real_escape_string($link, $_POST['edu_system']);
	$branch = mysqli_real_escape_string($link, $_POST['branch']);
	$course = mysqli_real_escape_string($link, $_POST['course']);

	$initial_pass = mysqli_real_escape_string($link, md5('1234' . 'qldsv'));
	$sql = "INSERT INTO sinh_vien SET mssv='$student_id',
									  mat_ma='$initial_pass',
									  ho_ten='$name',
									  ngay_sinh='$birthday',
									  he_dao_tao='$edu_system',
									  nganh_hoc='$branch',
									  khoa_hoc='$course'";

	if (!mysqli_query($link, $sql))
	{
		$_SESSION['error'] = 'Có lỗi khi thêm dữ liệu! Vui lòng kiểm tra lại!';
		header('Location: .');
		exit();
		//$error = 'Error adding submitted student.';
	}

	header('Location: .');
	exit();
}

if (isset($_POST['action']) and $_POST['action'] == 'Sửa SV')
{
	$student_id = mysqli_real_escape_string($link, $_POST['student_id']);
	$sql = "SELECT * FROM sinh_vien WHERE mssv='$student_id'";
	$result = mysqli_query($link, $sql);
	if (!$result)
	{
		$error = 'Error fetching student details.';
		include './includes/error.html.php';
		exit();
	}
	$row = mysqli_fetch_array($result);

	$_SESSION['pagetitle'] = 'Sửa thông tin sinh viên';
	$_SESSION['action'] = 'edit_student_form';
	$_SESSION['student_id'] = $row['mssv'];
	$_SESSION['name'] = $row['ho_ten'];
	$_SESSION['birthday'] = $row['ngay_sinh'];
	$_SESSION['edu_system'] = $row['he_dao_tao'];
	$_SESSION['branch'] = $row['nganh_hoc'];
	$_SESSION['course'] = $row['khoa_hoc'];
	$_SESSION['button'] = 'Sửa sinh viên';
}

if (isset($_GET['edit_student_form']))
{
	$old_student_id = mysqli_real_escape_string($link, $_POST['old_student_id']);
	$student_id = mysqli_real_escape_string($link, $_POST['student_id']);
	$name = mysqli_real_escape_string($link, $_POST['name']);
	$birthday = mysqli_real_escape_string($link, $_POST['birthday']);
	$edu_system = mysqli_real_escape_string($link, $_POST['edu_system']);
	$branch = mysqli_real_escape_string($link, $_POST['branch']);
	$course = mysqli_real_escape_string($link, $_POST['course']);


	$sql = "UPDATE sinh_vien SET mssv='$student_id',
							     ho_ten='$name',
								 ngay_sinh='$birthday',
								 he_dao_tao='$edu_system',
								 nganh_hoc='$branch',
								 khoa_hoc='$course'					
								 WHERE mssv='$old_student_id'";

	if (!mysqli_query($link, $sql))
	{
		$_SESSION['error'] = 'Có lỗi khi cập nhật dữ liệu! Vui lòng kiểm tra lại!';
		header('Location: .');
		exit();
		//$error = 'Error updating submitted student.';
	}

	header('Location: .');
	exit();
}

if (isset($_POST['action']) and $_POST['action'] == 'Xóa SV')
{
	$student_id = mysqli_real_escape_string($link, $_POST['student_id']);
	$sql = "DELETE FROM diem WHERE mssv = '$student_id'";
	$result = mysqli_query($link, $sql);
	if(!$result)
	{
		$_SESSION['error'] = 'Có lỗi khi xóa dữ liệu! Vui lòng kiểm tra lại!';
		header('Location: .');
		exit();
		//$error = 'Error deleting rows in score table';
	}

	$sql = "DELETE FROM sinh_vien WHERE mssv = '$student_id'";
	$result = mysqli_query($link, $sql);
	if(!$result)
	{
		$_SESSION['error'] = 'Có lỗi khi xóa dữ liệu! Vui lòng kiểm tra lại!';
		header('Location: .');
		exit();
		//$error = 'Error deleting student';
	}

	header('Location: .');
	exit();
}
?>